﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.Utils.Menu;
using DevExpress.XtraEditors;
using ERP_RORZE_DLL.Product.DAO;
using ERP_RORZE_DLL.Product.database;
using ERP_RORZE_DLL.Product.Base;
using System.Data.OleDb;
using ERP_RORZE_PERMISSION.Base;
using ERP_RORZE_PRODUCT.Product.BusOder;
using ERP_RORZE_SYSTEM;
using System.Data.Common;
using System.Data.SqlClient;
using Utilities = ERP_RORZE_TOOL.Common.Utilities;

namespace ERP_RORZE_PRODUCT.Product.PartHandling
{
    public partial class ctrlInspF3Detail : DevExpress.XtraEditors.XtraUserControl
    {
        ClsPartHandlingDAO clsDAO = new ClsPartHandlingDAO();
        private LogUtils log;
        ClsDInspF3Table clsobj = new ClsDInspF3Table();
        DataTable dt;
        DXPopupMenu menu;
        private int range = 1;

        public ctrlInspF3Detail()
        {
            InitializeComponent();
            log = new LogUtils();

            InitDateRangeMenu();
            this.dropbtDateRange.DropDownControl = menu;
            this.dropbtDateRange.Text = "3 Months";
        }

        void Reload(object sender, EventArgs e)
        {
            BindDatatoGrid(range);
        }
        private void btnReload_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            //if (MessageBox.Show("Bạn có thực sự muốn xóa dữ liệu!", "Message", MessageBoxButtons.OKCancel,
            //                    MessageBoxIcon.Question) == DialogResult.OK)
            //{
            //    clsobj.ID = int.Parse(gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "ID").ToString());
            //    clsDAO.DeleteInspF3Detail(clsobj);
            //    Reload(sender, e);
            //}

            if (gridView1.SelectedRowsCount > 0)
            {
                var row = new DataRow[gridView1.SelectedRowsCount];
                var laborId = new string[gridView1.SelectedRowsCount];

                for (int i = 0; i < gridView1.SelectedRowsCount; i++)
                {
                    row[i] = gridView1.GetDataRow(gridView1.GetSelectedRows()[i]);
                    laborId[i] = row[i]["ID"].ToString();
                }

                if (MessageBox.Show("Bạn có thực sự muốn xóa dữ liệu!", "Message", MessageBoxButtons.OKCancel,
                                MessageBoxIcon.Question) == DialogResult.OK)
                {
                    for (int i = 0; i < laborId.Length; i++)
                    {
                        try
                        {
                            clsobj.ID = int.Parse(laborId[i]);
                            clsDAO.DeleteInspF3Detail(clsobj);

                            //write Log
                            log.Table = LogConstant.Table.Product.Insp_F3;
                            log.Task = LogConstant.Task.Delete;
                            log.Description = "MONo: " + gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "MONo") + "; Date: " + gridView1.GetRowCellValue(gridView1.FocusedRowHandle, "date");
                            log.WriteLog();

                        }
                        catch (Exception ex)
                        {

                            MessageBox.Show("Error: " + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

                        }
                    }
                    Reload(sender, e);
                    MessageBox.Show("Đã xóa!");

                }
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;
            frm_InspF3Input frmAdd = new frm_InspF3Input();
            frmAdd.Reload += new EventHandler(Reload);
            frmAdd.Show();
            Cursor.Current = Cursors.Default;

        }

        private void gridControl1_Load(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnRefresh_Click(object sender, EventArgs e)
        {
            Reload(sender, e);
        }

        private void btnAddNew_Click(object sender, EventArgs e)
        {
            btnAdd_Click(sender, e);
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            btnDelete_Click(sender, e);
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            clsConstantsFuction.ExportToExcel(gridView1, 1, true);
        }

        private void simpleButton1_Click(object sender, EventArgs e)
        {
            //FileDialog fileDialog = new OpenFileDialog();
            //fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            //if (fileDialog.ShowDialog() == DialogResult.OK)
            //{
            //    OleDbConnection connection = new OleDbConnection();
            //    try
            //    {
            //        string path = fileDialog.FileName;
            //        INISettings iniSettings = new INISettings();
            //        string connectString = iniSettings.SQLConnectionString;
            //        // Connection String to Excel Workbook
            //        string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

            //        connection.ConnectionString = excelConnectionString;
            //        OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
            //        connection.Open();
            //        // Create DbDataReader to Data Worksheet
            //        DbDataReader dr = command.ExecuteReader();

            //        // Bulk Copy to SQL Server 
            //        SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
            //        bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_DInspF3]";

            //        SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("date", "date");
            //        bulkInsert.ColumnMappings.Add(date);

            //        SqlBulkCopyColumnMapping toolId = new SqlBulkCopyColumnMapping("MONo", "MONo");
            //        bulkInsert.ColumnMappings.Add(toolId);

            //        SqlBulkCopyColumnMapping makerId = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
            //        bulkInsert.ColumnMappings.Add(makerId);

            //        SqlBulkCopyColumnMapping PartID = new SqlBulkCopyColumnMapping("F3OKQty", "F3OKQty");
            //        bulkInsert.ColumnMappings.Add(PartID);

            //        SqlBulkCopyColumnMapping Qty = new SqlBulkCopyColumnMapping("F3SPQty", "F3SPQty");
            //        bulkInsert.ColumnMappings.Add(Qty);

            //        SqlBulkCopyColumnMapping Deadline = new SqlBulkCopyColumnMapping("F3NGQty", "F3NGQty");
            //        bulkInsert.ColumnMappings.Add(Deadline);

            //        SqlBulkCopyColumnMapping RawQty = new SqlBulkCopyColumnMapping("Note", "Note");
            //        bulkInsert.ColumnMappings.Add(RawQty);

            //        SqlBulkCopyColumnMapping HelisertQty = new SqlBulkCopyColumnMapping("Finished", "Finished");
            //        bulkInsert.ColumnMappings.Add(HelisertQty);

            //        bulkInsert.WriteToServer(dr);

            //        connection.Close();
            //        Reload(sender, e);
            //    }
            //    catch (Exception ex)
            //    {
            //        MessageBox.Show(ex.Message);
            //        connection.Close();
            //    }
            //}
            ImportData();
        }

        private void ImportData()
        {
            INISettings mySetting = new INISettings();
            SqlConnection myConnection = new SqlConnection(mySetting.SQLConnectionString);

            int countOk = 0;
            int countDup = 0;
            int countErr = 0;

            List<Utilities.ListItem> listDup = new List<Utilities.ListItem>();
            List<Utilities.ListItem> listErr = new List<Utilities.ListItem>();

            FileDialog importFile = new OpenFileDialog();
            importFile.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (importFile.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection excelConnection = null;
                try
                {
                    Control parent = progressPanel1.Parent;
                    progressPanel1.Location =
                        new Point(parent.Bounds.X + parent.Bounds.Width / 2 - progressPanel1.Width / 2,
                                  parent.Bounds.Y + parent.Bounds.Height / 2 - progressPanel1.Height / 2);
                    progressPanel1.Visible = true;
                    string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                                                   importFile.FileName +
                                                   ";Extended Properties=Excel 12.0;Persist Security Info=False";
                    excelConnection = new OleDbConnection(excelConnectionString);
                    OleDbCommand cmd =
                        new OleDbCommand("select [Date],[MONo],[StaffID],[F3OKQty],[F3SPQty]" +
                                         ",[F3NGQty],[Note],[Finished] from [Sheet1$]",
                                         excelConnection);
                    excelConnection.Open();
                    OleDbDataReader dReader;
                    dReader = cmd.ExecuteReader();


                    int line = 1;
                    string lineDup = string.Empty;

                    string Date = string.Empty;
                    string MONo = string.Empty;
                    string StaffID = string.Empty;
                    string F3OKQty = string.Empty;
                    string F3SPQty = string.Empty;
                    string F3NGQty = string.Empty;
                    string Note = string.Empty;
                    string Finished = string.Empty;


                    myConnection.Open();
                    while (dReader.Read())
                    {
                        bool exist = false;
                        line++;
                        try
                        {
                            string dt = dReader["Date"].ToString().Trim();
                            if (dReader["MONo"].ToString().Trim().Equals(string.Empty)
                                && dReader["StaffID"].ToString().Trim().Equals(string.Empty)
                                && dReader["F3OKQty"].ToString().Trim().Equals(string.Empty)
                                && dReader["F3SPQty"].ToString().Trim().Equals(string.Empty)
                                && dReader["F3NGQty"].ToString().Trim().Equals(string.Empty)
                                && dReader["Note"].ToString().Trim().Equals(string.Empty)
                                && dReader["Finished"].ToString().Trim().Equals(string.Empty)
                                )
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"].ToString(), "Null line"));
                                exist = true;
                            }
                        }
                        catch (Exception e)
                        {
                            countErr++;
                            listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"].ToString(), e.Message));
                            exist = true;
                        }

                        if (!exist)
                        {
                            try
                            {
                                Date = (dReader["Date"].ToString().Equals(string.Empty)) ? "null" : "'" + DateTime.Parse(dReader["Date"].ToString()).ToShortDateString() + "'";
                                MONo = (dReader["MONo"].ToString().Equals(string.Empty)) ? "null" : "'" + (dReader["MONo"].ToString()) + "'";
                                StaffID = (dReader["StaffID"].ToString().Equals(string.Empty)) ? "null" : "'" + dReader["StaffID"].ToString() + "'";
                                F3OKQty = (dReader["F3OKQty"].ToString().Equals(string.Empty)) ? "null" : dReader["F3OKQty"].ToString();
                                F3SPQty = (dReader["F3SPQty"].ToString().Equals(string.Empty)) ? "null" : dReader["F3SPQty"].ToString();
                                F3NGQty = (dReader["F3NGQty"].ToString().Equals(string.Empty)) ? "null" : dReader["F3NGQty"].ToString();
                                Note = (dReader["Note"].ToString().Equals(string.Empty)) ? "null" : dReader["Note"].ToString();
                                Finished = (dReader["Finished"].ToString().Equals(string.Empty)) ? "null" : "'" + dReader["Finished"].ToString() + "'";
                            }
                            catch (Exception ex)
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"].ToString(), ex.Message));
                                exist = true;

                            }
                        }

                        if (!exist)
                        {
                            try
                            {
                                string sql = "INSERT INTO [242_DInspF3] ([Date],[MONo],[StaffID],[F3OKQty]" +
                                         ",[F3SPQty],[F3NGQty],[Note],[Finished])"
                                         + " VALUES (" + Date + ", " + MONo + ", " + StaffID + ","
                                         + F3OKQty + "," + F3SPQty + ", " + F3NGQty + ", " + Note + ", " + Finished + ")";
                                SqlCommand cmd_sql = new SqlCommand(sql, myConnection);

                                cmd_sql.ExecuteNonQuery();
                                cmd_sql.Dispose();
                                countOk++;

                                //write Log
                                log.Table = LogConstant.Table.Product.Insp_F3;
                                log.Task = LogConstant.Task.Import;
                                log.Description = "MONo: " + MONo + "; Date: " + Date;
                                log.WriteLog();

                            }
                            catch (Exception ex)
                            {
                                countErr++;
                                listErr.Add(new Utilities.ListItem(line.ToString(), dReader["MONo"].ToString(), ex.Message));

                            }
                        }
                    }
                    myConnection.Close();
                    progressPanel1.Visible = false;
                    excelConnection.Close();
                    //MessageBox.Show(countOk + " records have been imported. \n" + countDup.ToString() + " record(s) duplicated (Lines: " + lineDup + ")");
                    //LoadData();
                    Frm_ImportReport report = new Frm_ImportReport(countOk, listDup, listErr, "InspF3");
                    report.StartPosition = FormStartPosition.CenterScreen;
                    report.Show();
                    report.Activate();
                }
                catch (Exception ex)
                {
                    if (excelConnection.State == ConnectionState.Open)
                    {
                        excelConnection.Close();
                    }
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    progressPanel1.Visible = false;
                    MessageBox.Show(ex.Message);
                }


            }
        }

        //Date range
        protected virtual void InitDateRangeMenu()
        {
            menu = new DXPopupMenu();
            menu.Items.Add(new DXMenuItem("3 Months", new EventHandler(OnItemClick3Month), null, null, null, null));
            menu.Items.Add(new DXMenuItem("6 Months", new EventHandler(OnItemClick6Months), null, null, null, null));
            menu.Items.Add(new DXMenuItem("One Year", new EventHandler(OnItemClick1Year), null, null, null, null));
            menu.Items.Add(new DXMenuItem("All time", new EventHandler(OnItemClickAllTime), null, null, null, null));

        }

        void OnItemClick3Month(object sender, EventArgs e)
        {
            BindDatatoGrid(1);
            range = 1;
            dropbtDateRange.Text = "3 Months";
        }

        void OnItemClick6Months(object sender, EventArgs e)
        {
            BindDatatoGrid(2);
            range = 2;
            dropbtDateRange.Text = "6 Months";
        }

        void OnItemClick1Year(object sender, EventArgs e)
        {
            BindDatatoGrid(3);
            range = 3;
            dropbtDateRange.Text = "One Year";
        }

        void OnItemClickAllTime(object sender, EventArgs e)
        {
            BindDatatoGrid(0);
            range = 0;
            dropbtDateRange.Text = "All time";
        }

        private void BindDatatoGrid(int range = 1)
        {
            Cursor.Current = Cursors.WaitCursor;
            dt = clsDAO.GetAllInspF3DetailRange(range);
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();

            Cursor.Current = Cursors.Default;

        }

    }
}
